create or replace TYPE TCP_CONNECTION as object (
    --Duplicate UTL_TCP.CONNECTION
    remote_host   VARCHAR2(255),   	-- Remote host name
    remote_port   INTEGER,     		-- Remote port INTEGER	
    local_host    VARCHAR2(255),   	-- Local host name
    local_port    INTEGER,     		-- Local port INTEGER
    charset       VARCHAR2(30),    	-- Character set for on-the-wire comm.
    newline       VARCHAR2(2),     	-- Newline character sequence
    tx_timeout    INTEGER,     		-- Transfer time-out value (in seconds)
    private_sd    INTEGER,	      	-- For internal use only
	
	-- whether this connection is open or not
	is_open		INTEGER,	  		
	
	CONSTRUCTOR FUNCTION TCP_CONNECTION RETURN SELF AS RESULT,
	
	member FUNCTION available( timeout IN INTEGER DEFAULT 0)
                     RETURN INTEGER,
                     
	member procedure open_connection(remote_host     IN VARCHAR2,
                           remote_port     IN INTEGER,
                           proxy		   IN VARCHAR2 DEFAULT NULL, -- Proxy server
                           local_host      IN VARCHAR2    DEFAULT NULL,
                           local_port      IN INTEGER DEFAULT NULL,
                           in_buffer_size  IN INTEGER DEFAULT NULL,
                           out_buffer_size IN INTEGER DEFAULT NULL,
                           charset         IN VARCHAR2    DEFAULT NULL,
                           tx_timeout      IN INTEGER DEFAULT NULL),
	
	member function read_raw(data IN OUT NOCOPY RAW,
		   					 len  IN            INTEGER DEFAULT 1,
                    		 peek IN            BOOLEAN     DEFAULT FALSE)
                    		 RETURN INTEGER,
							 
	member function write_raw(data IN            RAW,
                     len  IN     INTEGER DEFAULT NULL)
                     RETURN INTEGER,
                     
	member function read_text(data IN OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
                     		  len  IN            INTEGER DEFAULT 1,
                     		  peek IN            BOOLEAN     DEFAULT FALSE)
                     		  RETURN INTEGER,
	
	member FUNCTION write_text(data IN            VARCHAR2 CHARACTER SET ANY_CS,
                      		   len  IN            INTEGER DEFAULT NULL)
                      		   RETURN INTEGER,
                      		   
	member FUNCTION read_line( data        IN OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
                     		   remove_crlf IN            BOOLEAN DEFAULT FALSE,
                     		   peek        IN            BOOLEAN DEFAULT FALSE)
                     		   RETURN INTEGER,
                     		   
	member FUNCTION write_line( data IN VARCHAR2  CHARACTER SET ANY_CS DEFAULT NULL)
                      RETURN INTEGER,
	
	member PROCEDURE flush,
	member PROCEDURE close_connection,
	static procedure close_all_connections
						                        
)NOT FINAL;
/
show err;


create or replace type body TCP_CONNECTION
as
	CONSTRUCTOR FUNCTION TCP_CONNECTION RETURN SELF AS RESULT
	IS
	BEGIN
		self.is_open := 0;
		RETURN;
	END;
	
	member FUNCTION available( timeout IN INTEGER DEFAULT 0)
                     RETURN INTEGER
    is
    	conn UTL_TCP.connection; 
    	retval pls_integer;
    begin
    	conn.remote_host :=  self.remote_host;																																					
    	conn.remote_port :=  self.remote_port;																																					
    	conn.local_host  :=  self.local_host ;																																					
	    conn.local_port  :=  self.local_port ;																																					
	    conn.charset     :=  self.charset    ;																																					
	    conn.newline     :=  self.newline    ;																																					
	    conn.tx_timeout  :=  self.tx_timeout ;																																					
	    conn.private_sd  :=  self.private_sd ;		
	    
	    retval := utl_tcp.available(conn, timeout);
	    
	    self.remote_host :=  conn.remote_host;																																					
    	self.remote_port :=  conn.remote_port;																																					
    	self.local_host  :=  conn.local_host ;																																					
	    self.local_port  :=  conn.local_port ;																																					
	    self.charset     :=  conn.charset    ;																																					
	    self.newline     :=  conn.newline    ;																																					
	    self.tx_timeout  :=  conn.tx_timeout ;																																					
	    self.private_sd  :=  conn.private_sd ;	
	    
	    return retval;
    end; 
                        
	member procedure open_connection(remote_host     IN VARCHAR2,
                           remote_port     IN INTEGER,
                           proxy		   IN VARCHAR2 DEFAULT NULL,
                           local_host      IN VARCHAR2    DEFAULT NULL,
                           local_port      IN INTEGER DEFAULT NULL,
                           in_buffer_size  IN INTEGER DEFAULT NULL,
                           out_buffer_size IN INTEGER DEFAULT NULL,
                           charset         IN VARCHAR2    DEFAULT NULL,
                           tx_timeout      IN INTEGER DEFAULT NULL)
	is                              
		conn UTL_TCP.connection;	
	begin                           
		conn := UTL_TCP.open_connection(	remote_host => remote_host, 
											remote_port => remote_port,
											local_host  => local_host,
                           					local_port  => local_port,
                           					in_buffer_size  => in_buffer_size,
                           					out_buffer_size => out_buffer_size,
                           					charset  =>  charset,
                           					tx_timeout  => tx_timeout);
		
		
		self.remote_host    :=  conn.remote_host;																																					
    	self.remote_port    :=  conn.remote_port;																																					
    	self.local_host     :=  conn.local_host ;																																					
	    self.local_port     :=  conn.local_port ;																																					
	    self.charset        :=  conn.charset    ;																																					
	    self.newline        :=  conn.newline    ;																																					
	    self.tx_timeout     :=  conn.tx_timeout ;																																					
	    self.private_sd     :=  conn.private_sd ;	
	    
	    self.is_open := 1;																																				
	end;  
	
	member function read_raw(data IN OUT NOCOPY RAW,
		   					 len  IN            INTEGER DEFAULT 1,
                    		 peek IN            BOOLEAN     DEFAULT FALSE)
                    		 RETURN INTEGER
    is
    	conn UTL_TCP.connection;
    	retval pls_integer;
    begin
    	conn.remote_host :=  self.remote_host;																																					
    	conn.remote_port :=  self.remote_port;																																					
    	conn.local_host  :=  self.local_host ;																																					
	    conn.local_port  :=  self.local_port ;																																					
	    conn.charset     :=  self.charset    ;																																					
	    conn.newline     :=  self.newline    ;																																					
	    conn.tx_timeout  :=  self.tx_timeout ;																																					
	    conn.private_sd  :=  self.private_sd ;		
	    
	    retval := utl_tcp.read_raw(conn, data, len, peek);
	    
	    self.remote_host :=  conn.remote_host;																																					
    	self.remote_port :=  conn.remote_port;																																					
    	self.local_host  :=  conn.local_host ;																																					
	    self.local_port  :=  conn.local_port ;																																					
	    self.charset     :=  conn.charset    ;																																					
	    self.newline     :=  conn.newline    ;																																					
	    self.tx_timeout  :=  conn.tx_timeout ;																																					
	    self.private_sd  :=  conn.private_sd ;	
	    
	    return retval;
	end;
							 
	member function write_raw(data IN RAW,
                     len  IN INTEGER DEFAULT NULL)
                     RETURN INTEGER
	is
		conn UTL_TCP.connection;
    	retval pls_integer;	
    begin
    	conn.remote_host :=  self.remote_host;																																					
    	conn.remote_port :=  self.remote_port;																																					
    	conn.local_host  :=  self.local_host ;																																					
	    conn.local_port  :=  self.local_port ;																																					
	    conn.charset     :=  self.charset    ;																																					
	    conn.newline     :=  self.newline    ;																																					
	    conn.tx_timeout  :=  self.tx_timeout ;																																					
	    conn.private_sd  :=  self.private_sd ;		
	    
	    retval := utl_tcp.write_raw(conn, data, len);
	    
	    self.remote_host :=  conn.remote_host;																																					
    	self.remote_port :=  conn.remote_port;																																					
    	self.local_host  :=  conn.local_host ;																																					
	    self.local_port  :=  conn.local_port ;																																					
	    self.charset     :=  conn.charset    ;																																					
	    self.newline     :=  conn.newline    ;																																					
	    self.tx_timeout  :=  conn.tx_timeout ;																																					
	    self.private_sd  :=  conn.private_sd ;	
	    
	    return retval;	
	end;
	
	member function read_text(data IN OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
                     		  len  IN            INTEGER DEFAULT 1,
                     		  peek IN            BOOLEAN     DEFAULT FALSE)
                     		  RETURN INTEGER
    is
    	conn UTL_TCP.connection;
    	retval pls_integer;	
    begin
    	conn.remote_host :=  self.remote_host;																																					
    	conn.remote_port :=  self.remote_port;																																					
    	conn.local_host  :=  self.local_host ;																																					
	    conn.local_port  :=  self.local_port ;																																					
	    conn.charset     :=  self.charset    ;																																					
	    conn.newline     :=  self.newline    ;																																					
	    conn.tx_timeout  :=  self.tx_timeout ;																																					
	    conn.private_sd  :=  self.private_sd ;		
	    
	    retval := utl_tcp.read_text(conn, data, len, peek);
	    
	    self.remote_host :=  conn.remote_host;																																					
    	self.remote_port :=  conn.remote_port;																																					
    	self.local_host  :=  conn.local_host ;																																					
	    self.local_port  :=  conn.local_port ;																																					
	    self.charset     :=  conn.charset    ;																																					
	    self.newline     :=  conn.newline    ;																																					
	    self.tx_timeout  :=  conn.tx_timeout ;																																					
	    self.private_sd  :=  conn.private_sd ;	
	    
	    return retval;	
	end;
	
	member FUNCTION write_text(data IN            VARCHAR2 CHARACTER SET ANY_CS,
                      		   len  IN            INTEGER DEFAULT NULL)
                      		   RETURN INTEGER
    is
    	conn UTL_TCP.connection;
    	retval pls_integer;	
    begin
    	conn.remote_host :=  self.remote_host;																																					
    	conn.remote_port :=  self.remote_port;																																					
    	conn.local_host  :=  self.local_host ;																																					
	    conn.local_port  :=  self.local_port ;																																					
	    conn.charset     :=  self.charset    ;																																					
	    conn.newline     :=  self.newline    ;																																					
	    conn.tx_timeout  :=  self.tx_timeout ;																																					
	    conn.private_sd  :=  self.private_sd ;		
	    
	    retval := utl_tcp.write_text(conn, data, len);
	    
	    self.remote_host :=  conn.remote_host;																																					
    	self.remote_port :=  conn.remote_port;																																					
    	self.local_host  :=  conn.local_host ;																																					
	    self.local_port  :=  conn.local_port ;																																					
	    self.charset     :=  conn.charset    ;																																					
	    self.newline     :=  conn.newline    ;																																					
	    self.tx_timeout  :=  conn.tx_timeout ;																																					
	    self.private_sd  :=  conn.private_sd ;	
	    
	    return retval;	
	end;
	
	
	member FUNCTION read_line( data        IN OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
                     		   remove_crlf IN            BOOLEAN DEFAULT FALSE,
                     		   peek        IN            BOOLEAN DEFAULT FALSE)
                     		   RETURN INTEGER
    is
    	conn UTL_TCP.connection;
    	retval pls_integer;	
    begin
    	conn.remote_host :=  self.remote_host;																																					
    	conn.remote_port :=  self.remote_port;																																					
    	conn.local_host  :=  self.local_host ;																																					
	    conn.local_port  :=  self.local_port ;																																					
	    conn.charset     :=  self.charset    ;																																					
	    conn.newline     :=  self.newline    ;																																					
	    conn.tx_timeout  :=  self.tx_timeout ;																																					
	    conn.private_sd  :=  self.private_sd ;		
	    
	    retval := utl_tcp.read_line(conn, data, remove_crlf, peek);
	    
	    self.remote_host :=  conn.remote_host;																																					
    	self.remote_port :=  conn.remote_port;																																					
    	self.local_host  :=  conn.local_host ;																																					
	    self.local_port  :=  conn.local_port ;																																					
	    self.charset     :=  conn.charset    ;																																					
	    self.newline     :=  conn.newline    ;																																					
	    self.tx_timeout  :=  conn.tx_timeout ;																																					
	    self.private_sd  :=  conn.private_sd ;	
	    
	    return retval;	
	end;
	
	member FUNCTION write_line( data IN VARCHAR2  CHARACTER SET ANY_CS DEFAULT NULL)
                      RETURN INTEGER
    is
    	conn UTL_TCP.connection;
    	retval pls_integer;	
    begin
    	conn.remote_host :=  self.remote_host;																																					
    	conn.remote_port :=  self.remote_port;																																					
    	conn.local_host  :=  self.local_host ;																																					
	    conn.local_port  :=  self.local_port ;																																					
	    conn.charset     :=  self.charset    ;																																					
	    conn.newline     :=  self.newline    ;																																					
	    conn.tx_timeout  :=  self.tx_timeout ;																																					
	    conn.private_sd  :=  self.private_sd ;		
	    
	    retval := utl_tcp.write_line(conn, data);
	    
	    self.remote_host :=  conn.remote_host;																																					
    	self.remote_port :=  conn.remote_port;																																					
    	self.local_host  :=  conn.local_host ;																																					
	    self.local_port  :=  conn.local_port ;																																					
	    self.charset     :=  conn.charset    ;																																					
	    self.newline     :=  conn.newline    ;																																					
	    self.tx_timeout  :=  conn.tx_timeout ;																																					
	    self.private_sd  :=  conn.private_sd ;	
	    
	    return retval;	
	end;
	
	member PROCEDURE flush
	is
    begin
    	conn.remote_host :=  self.remote_host;																																					
    	conn.remote_port :=  self.remote_port;																																					
    	conn.local_host  :=  self.local_host ;																																					
	    conn.local_port  :=  self.local_port ;																																					
	    conn.charset     :=  self.charset    ;																																					
	    conn.newline     :=  self.newline    ;																																					
	    conn.tx_timeout  :=  self.tx_timeout ;																																					
	    conn.private_sd  :=  self.private_sd ;		
	    
	    utl_tcp.flush(conn);
	    
	    self.remote_host :=  conn.remote_host;																																					
    	self.remote_port :=  conn.remote_port;																																					
    	self.local_host  :=  conn.local_host ;																																					
	    self.local_port  :=  conn.local_port ;																																					
	    self.charset     :=  conn.charset    ;																																					
	    self.newline     :=  conn.newline    ;																																					
	    self.tx_timeout  :=  conn.tx_timeout ;																																					
	    self.private_sd  :=  conn.private_sd ;	
	end;
	
	member PROCEDURE close_connection
	is
		conn UTL_TCP.connection;
    begin
    	conn.remote_host :=  self.remote_host;																																					
    	conn.remote_port :=  self.remote_port;																																					
    	conn.local_host  :=  self.local_host ;																																					
	    conn.local_port  :=  self.local_port ;																																					
	    conn.charset     :=  self.charset    ;																																					
	    conn.newline     :=  self.newline    ;																																					
	    conn.tx_timeout  :=  self.tx_timeout ;																																					
	    conn.private_sd  :=  self.private_sd ;		
	    
	    utl_tcp.close_connection(conn);
	    
	    self.remote_host :=  conn.remote_host;																																					
    	self.remote_port :=  conn.remote_port;																																					
    	self.local_host  :=  conn.local_host ;																																					
	    self.local_port  :=  conn.local_port ;																																					
	    self.charset     :=  conn.charset    ;																																					
	    self.newline     :=  conn.newline    ;																																					
	    self.tx_timeout  :=  conn.tx_timeout ;																																					
	    self.private_sd  :=  conn.private_sd ;	
	    
	    self.is_open := 0;
	end;
	
	static procedure close_all_connections
	is
    begin
    	utl_tcp.close_all_connections;    	
	end;
	                        
end;                   
/                      
show err;              